package org.jiuling.poi.tools;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jiuling.local.tools.verify.IntegerVerify;
import org.jiuling.local.tools.verify.MapVerify;
import org.jiuling.local.tools.verify.ObjectVerify;
import org.jiuling.poi.tools.exception.PioToolsException;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 表格导入
 * @author 紫灵
 * @date 2022年05月21日 11时20分19秒
 */
public class ExcelImport {



    public static List<List<Map<String,Object>>> importByInputStream(InputStream inputStream, Integer startRowNum, Map<Integer,String> valueLineNumMap) throws IOException, PioToolsException {
        if(IntegerVerify.isNull(startRowNum)){
            startRowNum = 0;
        }
        List<List<Map<String,Object>>> workbookList = new ArrayList<>();
        // 创建workbook对象
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 获取表数量
        int sheetNum = workbook.getNumberOfSheets();
        for (int currentSheetNum = 0; currentSheetNum < sheetNum; currentSheetNum++) {
            List<Map<String,Object>> sheetMapList = new ArrayList<>();
            XSSFSheet sheet = workbook.getSheetAt(currentSheetNum);
            // 获取表行数
            long rowNum = sheet.getPhysicalNumberOfRows();
            if(startRowNum > rowNum){
                throw new PioToolsException(sheet.getSheetName()+"开始行号大于总行号！");
            }

            // 遍历行，设置开始行号
            for (int currentRowNum = startRowNum; currentRowNum < rowNum-startRowNum; currentRowNum++) {
                Map<String,Object> valueMap = new HashMap<>();
                XSSFRow row = sheet.getRow(currentRowNum);
                if(ObjectVerify.isNull(row)){
                    continue;
                }
                if(MapVerify.isBlank(valueLineNumMap)){
                    // 获取列号
                    int cellNum = row.getPhysicalNumberOfCells();
                    for (int currentCellNum = 0; currentCellNum < cellNum; currentCellNum++) {
                        valueMap.put(String.valueOf(currentCellNum+1),getCellValue(row,currentCellNum));
                    }
                }else {
                    for (Map.Entry<Integer,String> valueLineNum : valueLineNumMap.entrySet()){
                        valueMap.put(String.valueOf(valueLineNum.getValue()),getCellValue(row,valueLineNum.getKey()-1));
                    }
                }
                sheetMapList.add(valueMap);
            }
            workbookList.add(sheetMapList);
        }
        return workbookList;
    }

    /**
     * 获取列数据
     * @param row 行
     * @param cellNum 列号
     * @return 数据
     */
    private static Object getCellValue(Row row, int cellNum) throws PioToolsException {
        Cell cell = row.getCell(cellNum);
        if(ObjectVerify.isNull(cell)){
            return null;
        }
        // 获取数据类型
        CellType cellType = cell.getCellType();
        Object value;
        try {
            switch (cellType) {
                case NUMERIC:
                    value = new DecimalFormat("#").format(cell.getNumericCellValue());
                    break;
                case STRING:
                case FORMULA:
                    value = cell.getStringCellValue();
                    break;
                case BLANK:
                    value = null;
                    break;
                default:
                    throw new PioToolsException("未知字段类型{}！行：{},列：{}" + cellType.name());
            }
        }catch (IllegalStateException e){
            throw new PioToolsException("未知字段类型{}！行：{},列：{}" + cellType.name());
        }
        return value;
    }

    public static void main(String[] args) throws IOException, PioToolsException {
        File file = new File("E:\\迅雷下载\\协议（网上商城）\\协议（网上商城）\\2022年水泥企业信息统计表.xlsx");
        Map<Integer,String> map = new HashMap<>();
        map.put(2,"userName");
        System.out.println(importByInputStream(new FileInputStream(file),1,map));
    }

}
